import pymysql
import traceback

class DButils:
    def __init__(self, host, user, passwd, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.database = database
        self.port = port
        self.charset = charset

    def _connect(self):
        """建立数据库连接"""
        return pymysql.connect(
            host=self.host,
            user=self.user,
            passwd=self.passwd,
            database=self.database,
            port=self.port,
            charset=self.charset
        )

    def insert(self, sql, data=None):
        """插入数据"""
        db = self._connect()
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                db.commit()
                print("数据插入成功")
        except Exception as e:
            db.rollback()
            print("数据插入失败:", e)
            traceback.print_exc()
        finally:
            db.close()

    def delete(self, sql, data=None):
        """删除数据"""
        db = self._connect()
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                db.commit()
                print("数据删除成功")
        except Exception as e:
            db.rollback()
            print("数据删除失败:", e)
            traceback.print_exc()
        finally:
            db.close()

    def update(self, sql, data=None):
        """更新数据"""
        db = self._connect()
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                db.commit()
                print("数据更新成功")
        except Exception as e:
            db.rollback()
            print("数据更新失败:", e)
            traceback.print_exc()
        finally:
            db.close()

    def select(self, sql, data=None, dict_result=False):
        """查询数据
        dict_result=True 时返回字典形式
        """
        db = self._connect()
        try:
            cursor_class = pymysql.cursors.DictCursor if dict_result else None
            with db.cursor(cursor_class) as cursor:
                cursor.execute(sql, data)
                result = cursor.fetchall()
                return result
        except Exception as e:
            print("数据查询失败:", e)
            traceback.print_exc()
        finally:
            db.close()


if __name__ == '__main__':
    db = DButils('127.0.0.1', 'root', 'root', 'company')

    # 插入
    insert_sql = "INSERT INTO t_dept(deptno, dname, loc) VALUES (%s, %s, %s)"
    db.insert(insert_sql, (8, '研发部', '上海'))

    # 查询
    select_sql = "SELECT * FROM t_dept"
    rows = db.select(select_sql, dict_result=True)
    print(rows)

    # 更新
    update_sql = "UPDATE t_dept SET loc=%s WHERE deptno=%s"
    db.update(update_sql, ('北京', 8))

    # 删除
    delete_sql = "DELETE FROM t_dept WHERE deptno=%s"
    db.delete(delete_sql, (8,))
